from scipy.optimize import linprog
import openpyxl
from collections import OrderedDict

workbook = openpyxl.load_workbook('data/算法大作业数据.xlsx')

"""----------------------饲料原料及其营养成分含量----------------------"""
worksheet = workbook['饲料原料及其营养成分含量']

data = OrderedDict()
for row in worksheet.iter_rows(values_only=True):
    if row[0] == None or type(row[2]) == str : continue
    if "%" in row[0]:
        data[row[0]] = [ n*0.01 for n in row[1:]]
    else:
        data[row[0]] = row[1:]

data.pop('等量使用（%）')
print(data)
"""----------------------生猪饲养标准----------------------"""
worksheet = workbook['生猪饲养标准']

rows = []
for row in worksheet.iter_rows(values_only=True): rows.append(row)
del rows[0]
cols = zip(*rows)
objects = [_ for _ in cols]
del objects[0]
del objects[-1]
# print(objects)

workbook.close()

# 目标函数的系数
c = data.pop('价格（元/kg）')  # 这里的值为目标函数中的饲料价格，例如玉米和麦麸的价格

# 材料比例的范围约束条件
bounds = [(low * 0.01, high * 0.01) for high, low in zip(data.pop('用量上限（%）'), data.pop('用量下限（%）')) ]

# print(bounds)
# print(data)

# 不等式约束条件的系数矩阵
A = [_ for _ in data.values()]

# print(A)
for b in objects:
    # 调用linprog函数求解线性规划问题
    res = linprog(c, A_ub=A, b_ub=b, bounds=bounds, method='simplex')
    # 输出结果
    print(b)
    print('最佳配方的饲料比例：', res.x)
    print('最低价格：', -res.fun)
